﻿#
#用于生成入库单草稿以便允许临期商品
#
#需要在.bahs_profile设置环境变量
#export PSI_PASSWORD=xxxxxxx
#export RDS_AGENT=yyyy
#
#手工在命令行运行 ruby fix_icbill.rb
#
require 'mysql2'
$WSID = '7E6C8D0D-E25A-11E8-8B92-6C92BF475963' #汉溪总仓

    rds = Mysql2::Client.new(:host => ENV['RDS_AGENT'], :username => "psi_root", :port => '1401', :password => ENV['PSI_PASSWORD'])

    priceHashP = {}; #Price hash in PSI goods_id

    #准备待登记商品表
    sql1 = "select * from psi.t_goods"
    res1 = rds.query(sql1)
    res1.each do |tgr|
        code = tgr['code']
        id = tgr['id']
        name = tgr['name']
        purchase_price = tgr['purchase_price'];
        if ( name.include? '临期' or name.include? '特惠' ) then
                priceHashP.store(id,purchase_price);
        end
    end
    puts "total near_eol: #{priceHashP.count}"

    #search goods already in warehouse which id='7E6C8D0D-E25A-11E8-8B92-6C92BF475963' #汉溪总仓
    sql2 = "select * from psi.t_inventory where warehouse_id = '7E6C8D0D-E25A-11E8-8B92-6C92BF475963';"
    res2 = rds.query(sql2)

    res2.each do |tgr|
        #从待登记表中移除已经存在于仓库总账的goods_id
        goods_id = tgr['goods_id']
        priceHashP.delete(goods_id)
    end


    #search goods already in pwbill draft
    sql3 = "select * from psi.t_pw_bill_detail where pwbill_id='8A9BFF2D-0326-11E9-8932-6C92BF475963'"
    res3 = rds.query(sql3)

    res3.each do |tgr|
        #从待登记表中移除已经存在于指定待入库单的goods_id
        goods_id = tgr['goods_id']
        priceHashP.delete(goods_id)
    end
    puts "to be generated: #{priceHashP.count}"

    priceHashP.each { |goods_id, purchase_price|
            #F605B51C-02AC-11E9-8932-6C92BF475963 ref#20181218007
            #PW20181219001 #8A9BFF2D-0326-11E9-8932-6C92BF475963
            #PW20181224001 C4BBC6F9-0733-11E9-8932-6C92BF475963
            goods_price=sprintf("%.2f",purchase_price)
            sqlu = "insert into psi.t_pw_bill_detail( 
                        id, date_created, 
                        goods_id, goods_count, goods_money, goods_price, 
                        pwbill_id, show_order, data_org, company_id
                    ) values( 
                        UPPER(UUID()), CURRENT_TIMESTAMP(), 
                        '#{goods_id}',0.0,0.0,#{goods_price},
                        'C4BBC6F9-0733-11E9-8932-6C92BF475963',  #pwbill_id
                        0,'01010001','4D74E1E4-A129-11E4-9B6A-782BCBD7746B'
                    );"
            resu = rds.query(sqlu)
    }

rds.close
